﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MOVIE_THEATER
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //CHECK HERE
        // set column name, and column width
        // set isolation level for transaction
        // set disable edit in datagridview
        private void reportBut_Click(object sender, EventArgs e)
        {
            //try
            //{
            //    int criteriaIndex = this.criteriaSBox.SelectedIndex;
            //    int dateTimeIndex = this.timeCriteriaSBox.SelectedIndex;

            //    string criteriaColName = "";
            //    string dateTimeColName = "Thời Gian";

            //    string dateSelCom = "";
            //    string dateGroupCom = "";
            //    string criteriaSelCom = "";
            //    string whereCom = "";
            //    string fromCom = "";
            //    string sumCom = "";

            //    switch (criteriaIndex)
            //    {
            //        case 0:
            //            criteriaSelCom = " SUM(CASE ve.NguoiLon_TreEm "
            //                            + " WHEN 1 THEN ( 1 - ctsc.GiaKhuyenMai ) * ctsc.GiaVeNguoiLon "
            //                            + " ELSE ( 1 - ctsc.GiaKhuyenMai ) * ctsc.GiaVeTreEm "
            //                            + " END) AS 'DoanhThu' ";
            //            fromCom = " FROM VE JOIN HOADON hd ON (ve.MaHD = hd.MaHD) "
            //                    + " JOIN CTSUATCHIEU ctsc ON (ve.MaCTSuatChieu = ctsc.MaCTSuatChieu) ";
            //            whereCom = " WHERE ve.TinhTrangVe = 3 ";
            //            sumCom = " SELECT  SUM(CASE ve.NguoiLon_TreEm "
            //                    + " WHEN 1 THEN ( 1 - ctsc.GiaKhuyenMai ) * ctsc.GiaVeNguoiLon "
            //                    + " ELSE ( 1 - ctsc.GiaKhuyenMai ) * ctsc.GiaVeTreEm "
            //                    + " END) AS 'Tong' "
            //                    + " FROM VE JOIN HOADON hd ON (ve.MaHD = hd.MaHD) "
            //                    + " JOIN CTSUATCHIEU ctsc ON (ve.MaCTSuatChieu = ctsc.MaCTSuatChieu) "
            //                    + " WHERE ve.TinhTrangVe = 3 ";
            //            criteriaColName = "Doanh Thu (VND)";
            //            break;
            //        case 1:
            //            criteriaSelCom = " COUNT(*) AS 'SoLuong' ";
            //            fromCom = " FROM    VE ve "
            //                        + " JOIN HOADON hd ON ( ve.MaHD = hd.MaHD ) ";
            //            whereCom = " WHERE   ve.TinhTrangVe = 3 ";
            //            sumCom = " SELECT COUNT(*) AS 'Tong' "
            //                        + " FROM VE "
            //                        + " WHERE ve.TinhTrangVe = 3 ";
            //            criteriaColName = "Số Lượng Vé";
            //            break;
            //        case 2:
            //            criteriaSelCom = " COUNT(*) AS 'SoLuong' ";
            //            fromCom = " FROM    VE ve "
            //                        + " JOIN HOADON hd ON ( ve.MaHD = hd.MaHD ) ";
            //            whereCom = " WHERE   ve.TinhTrangVe = 2 ";
            //            sumCom = " SELECT COUNT(*) AS 'Tong' "
            //                        + " FROM VE "
            //                        + " WHERE ve.TinhTrangVe = 2 ";
            //            criteriaColName = "Số Lượng Vé";
            //            break;
            //        default:
            //            MessageBox.Show("Tiêu chí thống kê không hợp lệ");
            //            return;
            //    }
            //    switch (dateTimeIndex)
            //    {
            //        case 0: dateSelCom = " CONVERT(VARCHAR, hd.NgayLap, 105) AS 'ThoiGian' ";
            //            dateGroupCom = " GROUP BY CONVERT(VARCHAR, hd.NgayLap, 105) ";
            //            break;
            //        case 1: dateSelCom = " CAST(MONTH(hd.NgayLap) AS VARCHAR) + '-' + CAST(YEAR(hd.NgayLap) AS VARCHAR) AS 'ThoiGian' ";
            //            dateGroupCom = " GROUP BY CAST(MONTH(hd.NgayLap) AS VARCHAR) + '-' + CAST(YEAR(hd.NgayLap) AS VARCHAR) ";
            //            break;
            //        case 2: dateSelCom = " YEAR(hd.NgayLap) AS 'ThoiGian' ";
            //            dateGroupCom = " GROUP BY YEAR(hd.NgayLap) ";
            //            break;
            //        default:
            //            MessageBox.Show("Thông tin thời gian không hợp lệ");
            //            return;
            //    }
            //    string query1 = " SELECT " + dateSelCom + " , " + criteriaSelCom + fromCom + whereCom + dateGroupCom;

            //    //CODE SOLVE FOR SERIALIZABLE CONFLICT
            //    //string delayQuery = " WAITFOR DELAY '0:0:10' ";
            //    //string isolationLevel = "SET TRAN ISOLATION LEVEL SERIALIZABLE ";
            //    //string[] queryArr = {isolationLevel, query1, delayQuery, sumCom };
            //    //bool[] isNoneQuery = {true, false, true,false };
                
            //    string[] queryArr = { query1, sumCom };
            //    bool[] isNoneQuery = { false, false };

            //    List<DataTable> result = SqlDataAccessHelper.executeTransaction(queryArr, isNoneQuery);
            //    this.reportGridView.DataSource = result[0];
            //    this.reportGridView.Columns[0].HeaderText = dateTimeColName;
            //    this.reportGridView.Columns[0].Width = 300;
            //    this.reportGridView.Columns[1].HeaderText = criteriaColName;
            //    this.reportGridView.Columns[1].Width = 300;
            //    if (result[1].Rows.Count != 0)
            //        this.totalLbl.Text = result[1].Rows[0]["Tong"].ToString();
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //}

            try
            {
                int criteriaIndex = this.criteriaSBox.SelectedIndex;
                int dateTimeIndex = this.timeCriteriaSBox.SelectedIndex;

                string spName = "";
                string sumCom = "";
                int dateType = 1;
                string criteriaColName = "";
                string dateTimeColName = "Thời Gian";

                switch (criteriaIndex)
                {
                    case 0:
                        spName = "sp_DoanhThuTheoThoiGian";
                        criteriaColName = "Doanh Thu (VND)";
                        sumCom = "sp_TongDoanhThu";
                        break;
                    case 1:
                        spName = "sp_SoVeDaThanhToanTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaThanhToan";
                        break;
                    case 2:
                        spName = "sp_SoVeDaDatTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaDat";
                        break;
                    default:
                        MessageBox.Show("Tiêu chí thống kê không hợp lệ");
                        return;
                }
                switch (dateTimeIndex)
                {
                    case 0:
                        dateType = 1;
                        break;
                    case 1: dateType = 2;
                        break;
                    case 2: dateType = 3;
                        break;
                    default:
                        MessageBox.Show("Thông tin thời gian không hợp lệ");
                        return;
                }
                string query1 = " EXEC " + spName + " " + dateType.ToString();
                sumCom = " EXEC " + sumCom;
                //CODE SOLVE FOR SERIALIZABLE CONFLICT
                //string delayQuery = " WAITFOR DELAY '0:0:10' ";
                //string isolationLevel = "SET TRAN ISOLATION LEVEL SERIALIZABLE ";
                //string[] queryArr = {isolationLevel, query1, delayQuery, sumCom };
                //bool[] isNoneQuery = {true, false, true,false };

                string[] queryArr = { query1, sumCom };
                bool[] isNoneQuery = { false, false };

                List<DataTable> result = SqlDataAccessHelper.executeTransaction(queryArr, isNoneQuery);
                this.reportGridView.DataSource = result[0];
                this.reportGridView.Columns[0].HeaderText = dateTimeColName;
                this.reportGridView.Columns[0].Width = 300;
                this.reportGridView.Columns[1].HeaderText = criteriaColName;
                this.reportGridView.Columns[1].Width = 300;
                if (result[1].Rows.Count != 0)
                    this.totalLbl.Text = result[1].Rows[0]["Tong"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void searchBut_Click(object sender, EventArgs e)
        {
            try
            {
                string email = this.emailTxtBox.Text.Trim();
                string phone = this.phoneTxtBox.Text.Trim();

                string query = " EXEC sp_TimKiemVe '" + email + "' , '" + phone + "' " ;                
                DataTable result = SqlDataAccessHelper.ExecuteQuery(query);
                if (result.Rows.Count == 0)
                {
                    this.ticketGridView.Rows.Clear();
                    MessageBox.Show("Không tìm thấy thông tin thỏa yêu cầu");
                    return;
                }

                for (int i = 0; i < result.Rows.Count; i++)
                {
                    this.ticketGridView.Rows.Add(new object[] {result.Rows[i]["MaVe"].ToString(), result.Rows[i]["STTChoNgoi"].ToString(), result.Rows[i]["MaDayPC"].ToString(), result.Rows[i]["TinhTrangVe"].ToString(), result.Rows[i]["MaHD"].ToString(), result.Rows[i]["MaCTSuatChieu"].ToString()});
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void emailTxtBox_MouseClick(object sender, MouseEventArgs e)
        {
            this.emailTxtBox.Clear();
        }

        private void phoneTxtBox_MouseClick(object sender, MouseEventArgs e)
        {
            this.phoneTxtBox.Clear();
        }

        private void deleteBut_Click(object sender, EventArgs e)
        {
            try
            {
                DataGridViewSelectedRowCollection selRows = this.ticketGridView.SelectedRows;
                if (selRows.Count == 0)
                {
                    MessageBox.Show("Bạn chưa chọn dòng nào để hủy vé");
                    return;
                }

                foreach (DataGridViewRow dr in selRows)
                {
                    string maVe = dr.Cells[0].Value.ToString();
                    SqlParameter para = new SqlParameter("@maVe", SqlDbType.VarChar);
                    para.Value = maVe;
                    SqlParameter[] paraArr = { para };
                    SqlDataAccessHelper.executeStoreProcedure("sp_huyVe", paraArr);
                    this.ticketGridView.Rows.Remove(dr);
                }
                MessageBox.Show("Hủy vé thành công");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }            
        }

        private void reportDelBut_Click(object sender, EventArgs e)
        {
            
        //CHECK HERE
        // set column name, and column width
        // set isolation level for transaction
        // set disable edit in datagridview
        
            try
            {
                int criteriaIndex = this.criteriaSBox.SelectedIndex;
                int dateTimeIndex = this.timeCriteriaSBox.SelectedIndex;

                string spName = "";
                string sumCom = "";
                int dateType = 1;
                string criteriaColName = "";
                string dateTimeColName = "Thời Gian";

                switch (criteriaIndex)
                {
                    case 0:
                        spName = "sp_DoanhThuTheoThoiGian";
                        criteriaColName = "Doanh Thu (VND)";
                        sumCom = "sp_TongDoanhThu";
                        break;
                    case 1:
                        spName = "sp_SoVeDaThanhToanTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaThanhToan";
                        break;
                    case 2:
                        spName = "sp_SoVeDaDatTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaDat";
                        break;
                    default:
                        MessageBox.Show("Tiêu chí thống kê không hợp lệ");
                        return;
                }
                switch (dateTimeIndex)
                {
                    case 0:
                        dateType = 1;
                        break;
                    case 1: dateType = 2;
                        break;
                    case 2: dateType = 3;
                        break;
                    default:
                        MessageBox.Show("Thông tin thời gian không hợp lệ");
                        return;
                }
                string query1 = " EXEC " + spName + " " + dateType.ToString();
                sumCom = " EXEC " + sumCom;
                //CODE SOLVE FOR SERIALIZABLE CONFLICT
                string delayQuery = " WAITFOR DELAY '0:0:10' ";
                //string isolationLevel = "SET TRAN ISOLATION LEVEL SERIALIZABLE ";
                //string[] queryArr = {isolationLevel, query1, delayQuery, sumCom };
                //bool[] isNoneQuery = {true, false, true,false };

                string[] queryArr = { query1,delayQuery, sumCom };
                bool[] isNoneQuery = { false, true, false };

                List<DataTable> result = SqlDataAccessHelper.executeTransaction(queryArr, isNoneQuery);
                this.reportGridView.DataSource = result[0];
                this.reportGridView.Columns[0].HeaderText = dateTimeColName;
                this.reportGridView.Columns[0].Width = 300;
                this.reportGridView.Columns[1].HeaderText = criteriaColName;
                this.reportGridView.Columns[1].Width = 300;
                if (result[1].Rows.Count != 0)
                    this.totalLbl.Text = result[1].Rows[0]["Tong"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void reportDelFixBut_Click(object sender, EventArgs e)
        {
            try
            {
                int criteriaIndex = this.criteriaSBox.SelectedIndex;
                int dateTimeIndex = this.timeCriteriaSBox.SelectedIndex;

                string spName = "";
                string sumCom = "";
                int dateType = 1;
                string criteriaColName = "";
                string dateTimeColName = "Thời Gian";

                switch (criteriaIndex)
                {
                    case 0:
                        spName = "sp_DoanhThuTheoThoiGian";
                        criteriaColName = "Doanh Thu (VND)";
                        sumCom = "sp_TongDoanhThu";
                        break;
                    case 1:
                        spName = "sp_SoVeDaThanhToanTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaThanhToan";
                        break;
                    case 2:
                        spName = "sp_SoVeDaDatTheoThoiGian";
                        criteriaColName = "Số Lượng Vé";
                        sumCom = "sp_TongSoVeDaDat";
                        break;
                    default:
                        MessageBox.Show("Tiêu chí thống kê không hợp lệ");
                        return;
                }
                switch (dateTimeIndex)
                {
                    case 0:
                        dateType = 1;
                        break;
                    case 1: dateType = 2;
                        break;
                    case 2: dateType = 3;
                        break;
                    default:
                        MessageBox.Show("Thông tin thời gian không hợp lệ");
                        return;
                }
                string query1 = " EXEC " + spName + " " + dateType.ToString();
                sumCom = " EXEC " + sumCom;
                //CODE SOLVE FOR SERIALIZABLE CONFLICT
                string delayQuery = " WAITFOR DELAY '0:0:10' ";
                string isolationLevel = "SET TRAN ISOLATION LEVEL SERIALIZABLE ";
                string[] queryArr = { isolationLevel, query1, delayQuery, sumCom };
                bool[] isNoneQuery = { true, false, true, false };

                //string[] queryArr = { query1, sumCom };
                //bool[] isNoneQuery = { false, false };

                List<DataTable> result = SqlDataAccessHelper.executeTransaction(queryArr, isNoneQuery);
                this.reportGridView.DataSource = result[0];
                this.reportGridView.Columns[0].HeaderText = dateTimeColName;
                this.reportGridView.Columns[0].Width = 300;
                this.reportGridView.Columns[1].HeaderText = criteriaColName;
                this.reportGridView.Columns[1].Width = 300;
                if (result[1].Rows.Count != 0)
                    this.totalLbl.Text = result[1].Rows[0]["Tong"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
                
    }
}
